<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Im_category_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_users(){
        return $this->db->query('SELECT * FROM app_users')->result();
    }

    public function get_currval(){
      return $this->db->query('SELECT get_nextval("CASE_NUM") as val')->result();
    }

    public function search_num($id){
      return $this->db->query('SELECT * FROM ar_account WHERE acct_num = "'.$id.'" ')->result();
    }

    public function get_inv_access($id){
        $sql = "SELECT * FROM inv_officer_access WHERE user_id = ".$id." ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_reqNum($val){
        $this->db->query("call get_request_num(?,@val)", $val);
        $que = $this->db->query("SELECT @val as val");
        return $que->result();
    }

    public function search($data){
        $arr = array();
        if(!empty($data['category_code'])){
          array_push($arr," category_code LIKE '". $data['category_code'] ."' ");
        }
        if(!empty($data['category_name'])){
          array_push($arr," category_name LIKE '". $data['category_name'] ."' ");
        }
        $sql = "WHERE ".implode(' AND ',$arr);
        $q = $this->db->query('SELECT * FROM im_cases_category_v '.$sql);
        return $q->result();
    }


    public function insert_category($data){
        $this->db->query("call im_category_ins(?,?,?,?,?,?,@id,@err,@msg)", $data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function update_category($data){
        $this->db->query("call im_category_upd(?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function insert_sub_category($data){
        $this->db->query("call im_sub_category_ins(?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function update_sub_category($data){
        $this->db->query("call im_sub_category_upd(?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function delete_sub_category($id){
        $this->db->query('DELETE FROM im_case_sub_categories WHERE sub_category_id = "'.$id.'"');
    }

    public function insert_history($data){
        $this->db->query("call im_case_history_ins(?,?,?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as err , @msg as msg");
        echo json_encode($que->result());
    }

    public function get_sub_category($id){
        return $this->db->query('SELECT * FROM im_cases_sub_category_v WHERE category_id = "'.$id.'" ')->result();
    }

    public function get_sub_category_by_id($id){
        return $this->db->query('SELECT * FROM im_cases_sub_category_v WHERE sub_category_id = "'.$id.'" ')->result();
    }
    public function get_categories($id){
        return $this->db->query('SELECT * FROM im_cases_category_v WHERE category_id = "'.$id.'" ')->result();
    }

    public function get_user_by_department($id){
      return $this->db->query('SELECT * FROM app_users WHERE department = "'.$id.'"')->result();
    }

    public function get_user_by_department_with_id($id){
      return $this->db->query('SELECT * FROM app_users WHERE department = "'.$id.'"')->result();
    }
}